package hc.appt.service;

import hc.appt.ApptDTO;
import hc.service.AService;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class ApptService extends AService {

	public ArrayList<ApptDTO> getApptList(String doctorId) {
		ArrayList<ApptDTO> apptList = new ArrayList<ApptDTO>();

		Statement sment = null;
		ResultSet rs = null;
		String sql = "SELECT a.*,H.HOSPITALNAME,  e.firstname AS docfname, e.lastname as doclname,  "
				+ "P.FIRSTNAME as patfname, P.LASTNAME as patlname  "
				+ "FROM scheduledetail a, employee e, employee p,hospital h "
				+ "WHERE a.doctorid = e.employeeid AND a.patientid = p.employeeid AND a.hospitalid = h.hospitalid"
				+ " and a.doctorid='" + doctorId + "'";
		System.out.println("sql=" + sql);
		try {
			sment = connection.createStatement();
			rs = sment.executeQuery(sql);
			while (rs != null && rs.next()) {
				ApptDTO e = ApptDTO.builderApptObj();
				// set appt
				e.getAppt().setApptId(rs.getInt("SCHEDULEID"));
				e.getAppt().setStatus(rs.getInt("ISAVAILABLE"));
				e.getAppt().setEmployeeId(rs.getString("DOCTORID"));
				e.getAppt().setPatientId(rs.getString("PATIENTID"));
				e.getAppt().setDate(rs.getDate("WORKDATE"));
				e.getAppt().setHospitalId(rs.getInt("HOSPITALID"));
				// set hospital name
				e.getHospital().setID(rs.getInt("HOSPITALID"));
				e.getHospital().setName(rs.getString("HOSPITALNAME"));
				// set employee
				e.getDoctor().setID(rs.getString("DOCTORID"));
				e.getDoctor().setLastName(rs.getString("doclname"));
				e.getDoctor().setFirstName(rs.getString("docfname"));
				// set patient
				e.getPatient().setPatientId(rs.getString("PATIENTID"));
				e.getPatient().setLastName(rs.getString("patlname"));
				e.getPatient().setFirstName(rs.getString("patfname"));

				apptList.add(e);
			}
		} catch (SQLException | NullPointerException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				sment.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return apptList;
	}
}
